﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;
using SIPO.Library.DomainObject.Administration.Catalog;

namespace SIPO.Library.DataAccess.Transaction
{
   public partial class JobDescResponsibilityRepo
    {
       public List<JobDescResponsibility> GetListByJobDescID(int jobDescID)
       {
           List<JobDescResponsibility> tt = new List<JobDescResponsibility>();
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "SELECT ResponsibilityID, JobDescriptionID, MainTask, Activities, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate FROM transactions.JobDescResponsibility WHERE JobDescriptionID = @JobDescriptionID ORDER BY  ResponsibilityID ASC";
                   cmd.CommandType = CommandType.Text;
                   cmd.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = jobDescID;
                   try
                   {
                       conn.Open();
                       using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                       {
                           while (dr.Read())
                           {
                               JobDescResponsibility t = new JobDescResponsibility();
                               t.ResponsibilityID = dr[JobDescResponsibility.Fields.ResponsibilityID] == DBNull.Value ? 0 : int.Parse(dr[JobDescResponsibility.Fields.ResponsibilityID].ToString());
                               t.JobDescriptionID = dr[JobDescResponsibility.Fields.JobDescriptionID] == DBNull.Value ? 0 : int.Parse(dr[JobDescResponsibility.Fields.JobDescriptionID].ToString());
                               t.MainTask = dr[JobDescResponsibility.Fields.MainTask] == DBNull.Value ? string.Empty : dr[JobDescResponsibility.Fields.MainTask].ToString();
                               t.Activities = dr[JobDescResponsibility.Fields.Activities] == DBNull.Value ? string.Empty : dr[JobDescResponsibility.Fields.Activities].ToString();
                               t.CreatedBy = dr[JobDescResponsibility.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[JobDescResponsibility.Fields.CreatedBy].ToString();
                               t.CreatedDate = dr[JobDescResponsibility.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescResponsibility.Fields.CreatedDate].ToString());
                               t.UpdatedBy = dr[JobDescResponsibility.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[JobDescResponsibility.Fields.UpdatedBy].ToString();
                               t.UpdatedDate = dr[JobDescResponsibility.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescResponsibility.Fields.UpdatedDate].ToString());
                               tt.Add(t);
                               t.Dispose();
                           }
                       }
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }
               }
           }
           return tt;
       }


       public List<JobDescResponsibility> GetListByJobDescID(int jobDescID,int programID)
       {
           List<JobDescResponsibility> tt = new List<JobDescResponsibility>();
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "SELECT ResponsibilityID, JobDescriptionID, MainTask, Activities, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate FROM transactions.JobDescResponsibility WHERE JobDescriptionID = @JobDescriptionID AND ProgramID = @ProgramID ORDER BY  ResponsibilityID ASC";
                   cmd.CommandType = CommandType.Text;
                   cmd.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = jobDescID;
                   cmd.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programID;
                   try
                   {
                       conn.Open();
                       using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                       {
                           while (dr.Read())
                           {
                               JobDescResponsibility t = new JobDescResponsibility();
                               t.ResponsibilityID = dr[JobDescResponsibility.Fields.ResponsibilityID] == DBNull.Value ? 0 : int.Parse(dr[JobDescResponsibility.Fields.ResponsibilityID].ToString());
                               t.JobDescriptionID = dr[JobDescResponsibility.Fields.JobDescriptionID] == DBNull.Value ? 0 : int.Parse(dr[JobDescResponsibility.Fields.JobDescriptionID].ToString());
                               t.MainTask = dr[JobDescResponsibility.Fields.MainTask] == DBNull.Value ? string.Empty : dr[JobDescResponsibility.Fields.MainTask].ToString();
                               t.Activities = dr[JobDescResponsibility.Fields.Activities] == DBNull.Value ? string.Empty : dr[JobDescResponsibility.Fields.Activities].ToString();
                               t.CreatedBy = dr[JobDescResponsibility.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[JobDescResponsibility.Fields.CreatedBy].ToString();
                               t.CreatedDate = dr[JobDescResponsibility.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescResponsibility.Fields.CreatedDate].ToString());
                               t.UpdatedBy = dr[JobDescResponsibility.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[JobDescResponsibility.Fields.UpdatedBy].ToString();
                               t.UpdatedDate = dr[JobDescResponsibility.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescResponsibility.Fields.UpdatedDate].ToString());
                               tt.Add(t);
                               t.Dispose();
                           }
                       }
                   }
                   catch (Exception ex)
                   {
                       throw (ex);
                   }
                   finally
                   {
                       if (conn.State == ConnectionState.Open) conn.Close();
                   }
               }
           }
           return tt;
       }		
    }
}
